package net.tycmc.cty.ctyms.timejob.iems.dao;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.ResourceBundle;

import com.tycmc.functions.util.NumberFormat;

import net.tycmc.bulb.common.dao.BaseDAO;
import net.tycmc.bulb.common.dao.IBaseDAO;
import net.tycmc.bulb.common.dao.ITransactionDAO;
import net.tycmc.bulb.common.dao.TransactionDAO;
import net.tycmc.bulb.common.datasource.ProxoolDataSource;
import net.tycmc.bulb.common.util.MapGetter;
import net.tycmc.bulb.common.util.StringUtil;
import net.tycmc.bulb.dbutil.global.GlobalDBVar;
import net.tycmc.cty.ctyms.timejob.common.DateRegUtil;
import net.tycmc.cty.ctyms.timejob.common.MyBaseDAO;
import net.tycmc.cty.ctyms.timejob.common.MyConnectDSDAO;
import net.tycmc.cty.ctyms.timejob.common.MyConnectDSTransDAO;

public class DrivingBehavior2DAO {
	private static String proxool = "proxool.test";
	
	private IBaseDAO baseDAO;
	
	private ITransactionDAO tranDAO;
	
	
	

	public ITransactionDAO getTranDAO() {
		return tranDAO;
	}

	public void setTranDAO(ITransactionDAO tranDAO) {
		this.tranDAO = tranDAO;
	}

	/**
	 * @return the baseDAO
	 */
	public IBaseDAO getBaseDAO() {
		return baseDAO;
	}

	/**
	 * @param baseDAO the baseDAO to set
	 */
	public void setBaseDAO(IBaseDAO baseDAO) {
		this.baseDAO = baseDAO;
	}
	
	/**
	 * 各设备发生总次数
	 * @param soft
	 * @param thisWorkDay
	 * @return
	 */
	public List<Map<String,Object>> getDriver2TotalCount(String soft,String thisWorkDay,String vclId){
		String tableDate = thisWorkDay.replaceAll("-", "");
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("select msgdb2_vcl_id,totalcnt = Isnull(Substring(mt,3,Len(mt)),0) from (SELECT  msgdb2_vcl_id, ");
		sbSql.append(" Max(Convert(VARCHAR(20),msgdb2_msgtype) + ',' + Cast(Substring(id,21,Len(id)) AS VARCHAR(8000))) mt FROM (");
		sbSql.append(" SELECT Max(Convert(VARCHAR(20),msgdb2_msgtime,120) + ',' + Cast(MsgDB2_Tcount AS VARCHAR(8000))) id, msgdb2_vcl_id,msgdb2_msgtype ");
		sbSql.append(" FROM "+GlobalDBVar.getDBFullName_Conn("Msg_DriverBehavior2_"+tableDate, "3", null) +" inner join V_VehicleInfo ON msgdb2_vcl_id = VehicleID where 1 = 1 ");
		if(StringUtil.isValid(vclId)){
			sbSql.append(" and vehicleid in("+vclId+")");
		}
		sbSql.append(" and (MsgDB2_MsgType = 2 or MsgDB2_MsgType is null) ");
		sbSql.append(" and usefullife >= Getdate() AND tmnl_softedition IN ("+soft+") group by msgdb2_vcl_id,msgdb2_msgtype ) s group by msgdb2_vcl_id)T ");
		return baseDAO.execQuery(sbSql.toString(), new Object[]{});
	}
	
	/**
	 * 各设备发生总次数(432)
	 * @param soft
	 * @param thisWorkDay
	 * @return
	 */
	public List<Map<String,Object>> getDriver2TotalCount432(String soft,String thisWorkDay,String vclId){
		String tableDate = thisWorkDay.replaceAll("-", "");
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("select msgdb2_vcl_id,totalcnt = Isnull(Substring(mt,3,Len(mt)),0) from (SELECT  msgdb2_vcl_id, ");
		sbSql.append(" Max(Convert(VARCHAR(20),msgdb2_msgtype) + ',' + Cast(Substring(id,21,Len(id)) AS VARCHAR(8000))) mt FROM (");
		sbSql.append(" SELECT Max(Convert(VARCHAR(20),msgdb2_msgtime,120) + ',' + Cast(MsgDB2_Tcount AS VARCHAR(8000))) id, msgdb2_vcl_id,msgdb2_msgtype ");
		sbSql.append(" FROM "+GlobalDBVar.getDBFullName_Conn("Msg_DriverBehavior2_"+tableDate, "3", null) +" inner join V_VehicleInfo ON msgdb2_vcl_id = VehicleID where 1 = 1 ");
		if(StringUtil.isValid(vclId)){
			sbSql.append(" and vehicleid in("+vclId+")");
		}
		sbSql.append(" and (MsgDB2_MsgType = 1 or MsgDB2_MsgType = 3) ");
		sbSql.append(" and usefullife >= Getdate() AND tmnl_softedition IN ("+soft+") group by msgdb2_vcl_id,msgdb2_msgtype ) s group by msgdb2_vcl_id)T ");
		return baseDAO.execQuery(sbSql.toString(), new Object[]{});
	}
	
	/**
	 * 湘邮502协议
	 * @param soft
	 * @param thisWorkDay
	 * @return
	 */
	public List<Map<String,Object>> getDriver2TotalCount502(String soft,String thisWorkDay,String vclId){
		String tableDate = thisWorkDay.replaceAll("-", "");
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("select msgdb2_vcl_id,totalcnt = Isnull(Substring(mt,3,Len(mt)),0) from (SELECT  msgdb2_vcl_id, ");
		sbSql.append(" Max(Convert(VARCHAR(20),msgdb2_msgtype) + ',' + Cast(Substring(id,21,Len(id)) AS VARCHAR(8000))) mt FROM (");
		sbSql.append(" SELECT Max(Convert(VARCHAR(20),msgdb2_msgtime,120) + ',' + Cast(MsgDB2_Tcount AS VARCHAR(8000))) id, msgdb2_vcl_id,msgdb2_msgtype ");
		sbSql.append(" FROM "+GlobalDBVar.getDBFullName_Conn("Msg_DriverBehavior2_"+tableDate, "3", null) +" inner join V_VehicleInfo ON msgdb2_vcl_id = VehicleID where 1 = 1 ");
		if(StringUtil.isValid(vclId)){
			sbSql.append(" and vehicleid in("+vclId+")");
		}
		sbSql.append(" and MsgDB2_MsgType = 3 ");
		sbSql.append(" and usefullife >= Getdate() AND tmnl_softedition IN ("+soft+") group by msgdb2_vcl_id,msgdb2_msgtype ) s group by msgdb2_vcl_id)T ");
		return baseDAO.execQuery(sbSql.toString(), new Object[]{});
	}
	/**
	 * 各设备各档位及速度分别发生次数
	 * @param soft
	 * @param thisWorkDay
	 * @return
	 */
	public List<Map<String,Object>> getDriver2SubCount(String soft,String thisWorkDay,String vclId){
		String tableDate = thisWorkDay.replaceAll("-", "");
		List<Map<String,Object>> listData = new ArrayList<Map<String,Object>>();
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("  select MsgDB2_Vcl_ID,tmnl_softedition,MsgDB2_SlidingTime,subcnt = Isnull(Substring(mt,3,Len(mt)),0) from ( ");
		sbSql.append(" select msgdb2_vcl_id,tmnl_softedition,MsgDB2_SlidingTime , max(convert(varchar(20),MsgDB2_MsgType)+','+ Cast(Substring(id,21,Len(id)) AS VARCHAR(max))) mt FROM (");
		sbSql.append(" SELECT Max(Convert(VARCHAR(20),msgdb2_msgtime,120) + ',' + Cast(MsgDB2_JsonContent AS VARCHAR(max))) id,msgdb2_vcl_id,msgdb2_msgtype,tmnl_softedition,MsgDB2_SlidingTime  FROM "+GlobalDBVar.getDBFullName_Conn("Msg_DriverBehavior2_"+tableDate, "3", null));
		sbSql.append(" INNER JOIN V_VehicleInfo ON msgdb2_vcl_id = VehicleID where 1 = 1 " );
		if(StringUtil.isValid(vclId)){
			sbSql.append(" and vehicleid in ("+vclId+")");
		}
		sbSql.append(" and (MsgDB2_MsgType = 2 or MsgDB2_MsgType is null ) and usefullife >= Getdate() AND tmnl_softedition IN ("+soft+") group by MsgDB2_Vcl_ID,MsgDB2_MsgType,tmnl_softedition,MsgDB2_SlidingTime  )S group by msgdb2_vcl_id,tmnl_softedition,MsgDB2_SlidingTime  )T ");
		listData = baseDAO.execQuery(sbSql.toString(), new Object[]{});
		return listData;
		
	}
	
	/**
	 * 各设备各档位及速度分别发生次数(432)
	 * @param soft
	 * @param thisWorkDay
	 * @return
	 */
	public List<Map<String,Object>> getDriver2SubCount432(String soft,String thisWorkDay,String vclId){
		String tableDate = thisWorkDay.replaceAll("-", "");
		List<Map<String,Object>> listData = new ArrayList<Map<String,Object>>();
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("  select MsgDB2_Vcl_ID,tmnl_softedition,MsgDB2_SlidingTime,subcnt = Isnull(Substring(mt,3,Len(mt)),0) from ( ");
		sbSql.append(" select msgdb2_vcl_id,tmnl_softedition,MsgDB2_SlidingTime , max(convert(varchar(20),MsgDB2_MsgType)+','+ Cast(Substring(id,21,Len(id)) AS VARCHAR(max))) mt FROM (");
		sbSql.append(" SELECT Max(Convert(VARCHAR(20),msgdb2_msgtime,120) + ',' + Cast(MsgDB2_JsonContent AS VARCHAR(max))) id,msgdb2_vcl_id,msgdb2_msgtype,tmnl_softedition,MsgDB2_SlidingTime  FROM "+GlobalDBVar.getDBFullName_Conn("Msg_DriverBehavior2_"+tableDate, "3", null));
		sbSql.append(" INNER JOIN V_VehicleInfo ON msgdb2_vcl_id = VehicleID where 1 = 1 " );
		if(StringUtil.isValid(vclId)){
			sbSql.append(" and vehicleid in ("+vclId+")");
		}
		sbSql.append(" and (MsgDB2_MsgType = 1 or MsgDB2_MsgType =3) and usefullife >= Getdate() AND tmnl_softedition IN ("+soft+") group by MsgDB2_Vcl_ID,MsgDB2_MsgType,tmnl_softedition,MsgDB2_SlidingTime )S group by msgdb2_vcl_id,tmnl_softedition,MsgDB2_SlidingTime  )T ");
		listData = baseDAO.execQuery(sbSql.toString(), new Object[]{});
		return listData;
		
	}
	
	/**
	 * 湘邮版本502协议
	 * @param soft
	 * @param thisWorkDay
	 * @return
	 */
	public List<Map<String,Object>> getDriver2SubCount502(String soft,String thisWorkDay,String vclId){
		String tableDate = thisWorkDay.replaceAll("-", "");
		List<Map<String,Object>> listData = new ArrayList<Map<String,Object>>();
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("  select MsgDB2_Vcl_ID,tmnl_softedition,MsgDB2_SlidingTime,subcnt = Isnull(Substring(mt,3,Len(mt)),0) from ( ");
		sbSql.append(" select msgdb2_vcl_id,tmnl_softedition,MsgDB2_SlidingTime , max(convert(varchar(20),MsgDB2_MsgType)+','+ Cast(Substring(id,21,Len(id)) AS VARCHAR(max))) mt FROM (");
		sbSql.append(" SELECT Max(Convert(VARCHAR(20),msgdb2_msgtime,120) + ',' + Cast(MsgDB2_JsonContent AS VARCHAR(max))) id,msgdb2_vcl_id,msgdb2_msgtype,tmnl_softedition,MsgDB2_SlidingTime  FROM "+GlobalDBVar.getDBFullName_Conn("Msg_DriverBehavior2_"+tableDate, "3", null));
		sbSql.append(" INNER JOIN V_VehicleInfo ON msgdb2_vcl_id = VehicleID where 1 = 1 and MsgDB2_JsonContent!='' " );
		if(StringUtil.isValid(vclId)){
			sbSql.append(" and vehicleid in ("+vclId+")");
		}
		sbSql.append(" and MsgDB2_MsgType =3 and usefullife >= Getdate() AND tmnl_softedition IN ("+soft+") group by MsgDB2_Vcl_ID,MsgDB2_MsgType,tmnl_softedition,MsgDB2_SlidingTime )S group by msgdb2_vcl_id,tmnl_softedition,MsgDB2_SlidingTime  )T ");
		listData = baseDAO.execQuery(sbSql.toString(), new Object[]{});
		return listData;
		
	}
	
	/**
	 * 获取设备参数设置信息
	 * 
	 * @param vclId
	 * @return
	 */
	public List<Map<String, Object>> getVclParaSetInfo(String thisWorkDay,String vclId) {
		StringBuffer strBuff = new StringBuffer();
		strBuff.append("select * from ParaSetDriver2 ");
		strBuff.append(" join ParaSetDetailedDriver2 on ParaSD2_ID=ParaSDD2_ParaSD2_ID ");
		strBuff.append(" where ParaSDD2_SetStatus=1");
		strBuff.append(" and ParaSD2_Vcl_ID in (select vehicleid from V_VehicleInfo  ");
		strBuff.append(" inner join (select MsgDB2_Vcl_ID from "+GlobalDBVar.getDBFullName_Conn("Msg_DriverBehavior2_"+thisWorkDay.replace("-", ""), "3", null));
		strBuff.append(" )a on VehicleID = MsgDB2_Vcl_ID where isnull(vehicleid,0)<>0 " );
		if(StringUtil.isValid(vclId)){
			strBuff.append(" and vehicleid in ("+vclId+") ");
		}
		
		strBuff.append("group by vehicleid)");
		return baseDAO.execQuery(strBuff.toString(), new Object[] {});
	}
	
	/**
	 * 获取颜色配置信息
	 * 
	 * @return
	 */
	public List<Map<String, Object>> getColorSetList() {
		String sql = "select * from ColorSet where CS_State=1 and CS_Type in(2,3) order by CS_Type";
		return baseDAO.execQuery(sql, new Object[] {});
	}
	
	/**
	 * 查询此设备是否配置 执行带档滑行
	 * 
	 * @return
	 */
	public boolean isSetDH(String vclId) {
		boolean isSet = false;
		String sql = "select count(*) as cn from GreadCoastingVcl where GreadCV_VclId=?";
		Map<String,Object>  map = new HashMap<String, Object>();
		map = baseDAO.execQueryOne(sql, new Object[] {vclId});
		if(Integer.parseInt(MapGetter.getString(map, "cn"))>0){
			isSet = true;
		}
		return isSet;
	}
	
	/**
	 * 统计各区域占比SQL拼接
	 * @param thisWorkDay
	 * @param data
	 * @param param
	 * @param vehicleid
	 * @return
	 */
	public void driverTwoSql(String thisWorkDay,Map<String, Object> data,Map<String,Map<String,Object>> param,String vehicleid,String[] dbTypelist,String dutyData,List<String>sqlList,String flag,String flag1){
		
		//设备ID总次数
		String totalCnt = MapGetter.getString(data, "totalCnt");
		String ddhxNums = MapGetter.getString(data, "DDHXNums");
		double toltal = 0;
		double area = 0;
		Map<String,Object>params=new HashMap<String, Object>();//求怠速及工作时长 参数集合
		params.put("s_time",thisWorkDay+" 00:00:00");
		params.put("e_time",thisWorkDay+" 23:59:59");
		params.put("VehicleID", vehicleid);
		List<Map<String,Object>> timeList =this.getECMTimeOfDefs(params);
		double daisu=0;//怠速时长
		double worktime=0;//工作时长
		double daisuPre=0;
		if(timeList!=null&&timeList.size()==2){
			if(!MapGetter.getString(timeList.get(1),"ecmTotalWorkTime").equals("0")){
				daisu=Double.parseDouble(MapGetter.getString(timeList.get(0),"ecmTotalTime"));
				worktime=Double.parseDouble(MapGetter.getString(timeList.get(1),"ecmTotalWorkTime"));	
				 }
		}
		if(worktime>0){
			daisuPre=NumberFormat.div(daisu*100, worktime,6);
		}
		
		if(!StringUtil.isValid(totalCnt)){
			totalCnt="0";
		}
		
		String color = "";
		String colorArea = "";
		String transtype = "";
		String times="";
		String time0G1S=MapGetter.getString(data,"ZG1S");//0G1S的次数
		times=MapGetter.getString(data,"0G1Stimes");
		String time0G3S=MapGetter.getString(data,"ZG3S");//0G3S的次数
		if(!StringUtil.isValid(time0G1S)){
			time0G1S="0";
		}
		if(!StringUtil.isValid(time0G3S)){
			time0G3S="0";
		}
		if(!StringUtil.isValid(ddhxNums)){
			ddhxNums="0";
		}
		sqlList.add("insert into VclDB2_"+thisWorkDay.substring(0, 7).replace("-", "")+" (VclDB2_Vcl_ID,VclDB2_BD_ID,VclDB2_Pre,VclDB2_iWork,VclDB2_StateTime,VclDB2_totalCnt,VclDB2_0G1STimes,VclDB2_0G3STimes) values("+vehicleid+","+3+","+daisuPre+","+worktime+",'"+thisWorkDay+"',"+totalCnt+","+time0G1S+","+time0G3S+");");
		if(!StringUtil.isValid(totalCnt) || "0".equals(totalCnt)){
			insertZero(thisWorkDay,vehicleid,dbTypelist,sqlList,worktime);
			return ;
		} else{
			toltal = Double.parseDouble(totalCnt);
		}
		
		double time0G1St=Double.parseDouble(times);//0G1S的次数
		double ddhxNum=Double.parseDouble(ddhxNums);//带档滑行的的次数
		double timesPre=0;//0G1S百分比
		double chazhi=0;//0G1S百分比与怠速的差值
		double ddhxPre = 0;//带档滑行百分比
		if(StringUtil.isValid(totalCnt) && !"0".equals(totalCnt)){
			timesPre=NumberFormat.div(time0G1St*100, toltal,4);
			ddhxPre = NumberFormat.div(ddhxNum*100, toltal,4);
		}
		if(timesPre>daisuPre){
			chazhi=timesPre-daisuPre;
		}
		for(String dbtype : dbTypelist){
			if("3".equals(dbtype)){
				continue;
			}
			transtype = this.getDB2Type(Integer.parseInt(dbtype));
			color = MapGetter.getString(param.get("2_"+transtype), "CS_Value");
			colorArea = MapGetter.getString(data, color);
			if(!StringUtil.isValid(colorArea)){
				sqlList.add("insert into VclDB2_"+thisWorkDay.substring(0, 7).replace("-", "")+" (VclDB2_Vcl_ID,VclDB2_BD_ID,VclDB2_Num,VclDB2_Pre,VclDB2_iWork,VclDB2_StateTime,VclDB2_totalCnt,VclDB2_0G1STimes,VclDB2_0G3STimes) values("+vehicleid+","+dbtype+",0,0,"+worktime+",'"+thisWorkDay+"',"+totalCnt+","+time0G1S+","+time0G3S+");");
				//高档低速为0，插入带档滑行区为0
				if("6".equals(dbtype)){
					if(!"true".equals(flag)&&!"true".equals(flag1)){
					sqlList.add("insert into VclDB2_"+thisWorkDay.substring(0, 7).replace("-", "")+" (VclDB2_Vcl_ID,VclDB2_BD_ID,VclDB2_Num,VclDB2_Pre,VclDB2_iWork,VclDB2_StateTime,VclDB2_totalCnt,VclDB2_0G1STimes,VclDB2_0G3STimes) values("+vehicleid+","+9+",0,0,"+worktime+",'"+thisWorkDay+"',"+totalCnt+","+time0G1S+","+time0G3S+");");
					}
				}
			}else if(!"7".equals(dbtype) && !"6".equals(dbtype)){//不为空挡低速h和高速抵挡区
				area = Double.parseDouble(colorArea);
				sqlList.add("insert into VclDB2_"+thisWorkDay.substring(0, 7).replace("-", "")+" (VclDB2_Vcl_ID,VclDB2_BD_ID,VclDB2_Num,VclDB2_Pre,VclDB2_iWork,VclDB2_StateTime,VclDB2_totalCnt,VclDB2_0G1STimes,VclDB2_0G3STimes) values("+vehicleid+","+dbtype+","+(int)area+","+NumberFormat.deleteString0(NumberFormat.div(area*100, toltal,2))+","+worktime+",'"+thisWorkDay+"',"+totalCnt+","+time0G1S+","+time0G3S+");");
			}else if("7".equals(dbtype)){//空挡低速做特殊处理
				area = Double.parseDouble(colorArea);
				double KongG=area-time0G1St;
				sqlList.add("insert into VclDB2_"+thisWorkDay.substring(0, 7).replace("-", "")+" (VclDB2_Vcl_ID,VclDB2_BD_ID,VclDB2_Pre,VclDB2_iWork,VclDB2_StateTime,VclDB2_totalCnt,VclDB2_0G1STimes,VclDB2_0G3STimes) values("+vehicleid+","+dbtype+","+NumberFormat.deleteString0(NumberFormat.div(KongG*100, toltal,2)+chazhi)+","+worktime+",'"+thisWorkDay+"',"+totalCnt+","+time0G1S+","+time0G3S+");");
			}else if("6".equals(dbtype)){//高速抵挡区			
					if(StringUtil.isValid(dutyData)){
						String[] imgParaArr_Center = dutyData.replace("%", "").split(",");
						if(imgParaArr_Center.length<50){
							area = Double.parseDouble(colorArea);
							sqlList.add("insert into VclDB2_"+thisWorkDay.substring(0, 7).replace("-", "")+" (VclDB2_Vcl_ID,VclDB2_BD_ID,VclDB2_Pre,VclDB2_iWork,VclDB2_StateTime,VclDB2_totalCnt,VclDB2_0G1STimes,VclDB2_0G3STimes) values("+vehicleid+","+dbtype+","+NumberFormat.deleteString0(NumberFormat.div(area*100, toltal,2))+","+worktime+",'"+thisWorkDay+"',"+totalCnt+","+time0G1S+","+time0G3S+");");
							if(!"true".equals(flag)&&!"true".equals(flag1)){
							//带档滑行区
							sqlList.add("insert into VclDB2_"+thisWorkDay.substring(0, 7).replace("-", "")+" (VclDB2_Vcl_ID,VclDB2_BD_ID,VclDB2_Pre,VclDB2_iWork,VclDB2_StateTime,VclDB2_totalCnt,VclDB2_0G1STimes,VclDB2_0G3STimes) values("+vehicleid+","+9+","+0+","+worktime+",'"+thisWorkDay+"',"+totalCnt+","+time0G1S+","+time0G3S+");");
							}
						} else {
							area = Double.parseDouble(colorArea);
							//求工作循环图标四个固定区域值和
							double duTol = NumberFormat.add(NumberFormat.add(NumberFormat.add(Double.parseDouble(imgParaArr_Center[0]), Double.parseDouble(imgParaArr_Center[1])),Double.parseDouble(imgParaArr_Center[8])),Double.parseDouble(imgParaArr_Center[9]));
							if(NumberFormat.div(area*100, toltal,2)>duTol){
								sqlList.add("insert into VclDB2_"+thisWorkDay.substring(0, 7).replace("-", "")+" (VclDB2_Vcl_ID,VclDB2_BD_ID,VclDB2_Pre,VclDB2_iWork,VclDB2_StateTime,VclDB2_totalCnt,VclDB2_0G1STimes,VclDB2_0G3STimes) values("+vehicleid+","+dbtype+","+NumberFormat.deleteString0(duTol)+","+worktime+",'"+thisWorkDay+"',"+totalCnt+","+time0G1S+","+time0G3S+");");
								if(!"true".equals(flag)&&!"true".equals(flag1)){
								//带档滑行区
								sqlList.add("insert into VclDB2_"+thisWorkDay.substring(0, 7).replace("-", "")+" (VclDB2_Vcl_ID,VclDB2_BD_ID,VclDB2_Pre,VclDB2_iWork,VclDB2_StateTime,VclDB2_totalCnt,VclDB2_0G1STimes,VclDB2_0G3STimes) values("+vehicleid+","+9+","+NumberFormat.deleteString0(NumberFormat.sub(NumberFormat.div(area*100, toltal,2), duTol))+","+worktime+",'"+thisWorkDay+"',"+totalCnt+","+time0G1S+","+time0G3S+");");
								}
							} else {
								sqlList.add("insert into VclDB2_"+thisWorkDay.substring(0, 7).replace("-", "")+" (VclDB2_Vcl_ID,VclDB2_BD_ID,VclDB2_Pre,VclDB2_iWork,VclDB2_StateTime,VclDB2_totalCnt,VclDB2_0G1STimes,VclDB2_0G3STimes) values("+vehicleid+","+dbtype+","+NumberFormat.deleteString0(NumberFormat.div(area*100, toltal,2))+","+worktime+",'"+thisWorkDay+"',"+totalCnt+","+time0G1S+","+time0G3S+");");
								if(!"true".equals(flag)&&!"true".equals(flag)){
								//带档滑行区
								sqlList.add("insert into VclDB2_"+thisWorkDay.substring(0, 7).replace("-", "")+" (VclDB2_Vcl_ID,VclDB2_BD_ID,VclDB2_Pre,VclDB2_iWork,VclDB2_StateTime,VclDB2_totalCnt,VclDB2_0G1STimes,VclDB2_0G3STimes) values("+vehicleid+","+9+","+0+","+worktime+",'"+thisWorkDay+"',"+totalCnt+","+time0G1S+","+time0G3S+");");
								}
							}
							
						}
					} else {
						area = Double.parseDouble(colorArea);
						sqlList.add("insert into VclDB2_"+thisWorkDay.substring(0, 7).replace("-", "")+" (VclDB2_Vcl_ID,VclDB2_BD_ID,VclDB2_Pre,VclDB2_iWork,VclDB2_StateTime,VclDB2_totalCnt,VclDB2_0G1STimes,VclDB2_0G3STimes) values("+vehicleid+","+dbtype+","+NumberFormat.deleteString0(NumberFormat.div(area*100, toltal,2))+","+worktime+",'"+thisWorkDay+"',"+totalCnt+","+time0G1S+","+time0G3S+");");
						if(!"true".equals(flag)){
						//带档滑行区
						sqlList.add("insert into VclDB2_"+thisWorkDay.substring(0, 7).replace("-", "")+" (VclDB2_Vcl_ID,VclDB2_BD_ID,VclDB2_Pre,VclDB2_iWork,VclDB2_StateTime,VclDB2_totalCnt,VclDB2_0G1STimes,VclDB2_0G3STimes) values("+vehicleid+","+9+","+0+","+worktime+",'"+thisWorkDay+"',"+totalCnt+","+time0G1S+","+time0G3S+");");
						}
					}
					
				
			}
		}
		//432设备
		if("true".equals(flag)||"true".equals(flag1)){
			//带档滑行区
			sqlList.add("insert into VclDB2_"+thisWorkDay.substring(0, 7).replace("-", "")+" (VclDB2_Vcl_ID,VclDB2_BD_ID,VclDB2_Pre,VclDB2_iWork,VclDB2_StateTime,VclDB2_totalCnt,VclDB2_0G1STimes,VclDB2_0G3STimes) values("+vehicleid+","+9+","+ddhxPre+","+worktime+",'"+thisWorkDay+"',"+totalCnt+","+time0G1S+","+time0G3S+");");
		}
		return ;
	}
	
	private String getDB2Type(int dbtype) {
		switch(dbtype){
		case 1:
			return "8";    
		case 2:
			return "1";
		case 3:
			return "5";
		case 4:
			return "4";
		case 5:
			return "2";
		case 6:
			return "3";
		case 7:
			return "6";
		case 8:
			return "7";
		}
		return "0";
	}
	
	/**
	 * SQL集合批量执行
	 * @param sqlList
	 */
	public void executeSql(List<String> sqlList,String isbakTable){
		 MyConnectDSTransDAO mucdd=new MyConnectDSTransDAO();
		 mucdd.batch(sqlList);
		 //如果需要备份执行备份sql--优化二期
		 if(isbakTable.equals("1")){
			 MyConnectDSTransDAO connect=new MyConnectDSTransDAO(true);
			 boolean isbak = connect.batch(sqlList);
			 if(!isbak){
				 System.out.println("档位车速数据备份失败");
			 }
		 }
		 
	}

	public void insertZero(String thisWorkDay, String vehicleid, String[] dblist,List<String>sqlList,double worktime) {
		
		for(String dbtype : dblist){
			if(dbtype.equals("3")){
				continue;
			}
			sqlList.add("insert into VclDB2_"+thisWorkDay.substring(0, 7).replace("-", "")+" (VclDB2_iWork,VclDB2_Vcl_ID,VclDB2_BD_ID,VclDB2_Num,VclDB2_Pre,VclDB2_StateTime,VclDB2_totalCnt,VclDB2_0G1STimes,VclDB2_0G3STimes) values("+worktime+",'"+vehicleid+"','"+dbtype+"','0','0','"+thisWorkDay+"',0,0,0);");
		}
		sqlList.add("insert into VclDB2_"+thisWorkDay.substring(0, 7).replace("-", "")+" (VclDB2_iWork,VclDB2_Vcl_ID,VclDB2_BD_ID,VclDB2_Num,VclDB2_Pre,VclDB2_StateTime,VclDB2_totalCnt,VclDB2_0G1STimes,VclDB2_0G3STimes) values("+worktime+",'"+vehicleid+"','"+9+"','0','0','"+thisWorkDay+"',0,0,0);");
	}

	public String[] getDBType() {
		
		StringBuilder sql =new StringBuilder("select BD_ID from BehaviDict where BD_ID<=8");
		
		List<Map<String,Object>> list = baseDAO.execQuery(sql.toString(), new Object[] {});
		
		String[] result = new String[8];
		
		for(int i=0;null!=list&&i<list.size();i++){
			result[i] = MapGetter.getString(list.get(i), "BD_ID");
		}
		
		return result;
	}
	
	
	/**
	 * 校验指定日期的“档位车速统计表”是否存在，如果不存在，则进行创建
	 **/
	public boolean checkCreateRoadInfo(String thisWorkDay,String isbakTable) {
		boolean flag = false;
		String sql = "Select count(id) as counts FROM SysObjects Where name = 'VclDB2_"+thisWorkDay+"' and xtype = 'U'";
		String bakSql = "create table dbo.VclDB2_"+thisWorkDay+"(VclDB2_ID [int] IDENTITY(1,1) NOT NULL,"
		        +"VclDB2_Vcl_ID [int] NOT NULL,"
				+"VclDB2_BD_ID [int] NOT NULL,"
		        +"VclDB2_Num [int] NULL,"
		        +"VclDB2_0G1STIMES [int] NULL,"
		        +"VclDB2_0G3STIMES [int] NULL,"
		        +"VclDB2_TOTALCNT [int] NULL,"
				+"VclDB2_Pre [float] NOT NULL,"
				+"VclDB2_iWork [float] NOT NULL,"
				+"VclDB2_StateTime [datetime] NOT NULL) ON [PRIMARY]";
		if(Integer.parseInt(MapGetter.getString(this.getBaseDAO().execQueryOne(sql, new Object[]{}),"counts"))>0){
			flag = true;
		}else{
			sql = "create table dbo.VclDB2_"+thisWorkDay+"(VclDB2_ID [int] IDENTITY(1,1) NOT NULL,"
		        +"VclDB2_Vcl_ID [int] NOT NULL,"
				+"VclDB2_BD_ID [int] NOT NULL,"
		        +"VclDB2_Num [int] NULL,"
		        +"VclDB2_0G1STIMES [int] NULL,"
		        +"VclDB2_0G3STIMES [int] NULL,"
		        +"VclDB2_TOTALCNT [int] NULL,"
				+"VclDB2_Pre [float] NOT NULL,"
				+"VclDB2_iWork [float] NOT NULL,"
				+"VclDB2_StateTime [datetime] NOT NULL) ON [PRIMARY]";
			if(this.baseDAO.execUpdate(sql, new Object[]{})>-1){
				flag = true;
			}
		}
		//判断是否需要向备份数据库插入数据，如果需要则直接创建表--优化二期
		if(isbakTable.equals("1")){
			MyConnectDSDAO mucdd=new MyConnectDSDAO(true);
			int bakTable = mucdd.execUpdate(bakSql, new Object[]{});
			if(bakTable<0){
				System.out.println("备份档位车速表创建失败");
			}
		}
		return flag;	
	}
	/**
	 * @author songxiaohu
	 * @param params
	 * @return
	 */
	public List<Map<String, Object>> getECMTimeOfDefs(Map<String, Object> params) {
		String begin = MapGetter.getString(params, "s_time");
		String end = MapGetter.getString(params, "e_time");
		String vclid = MapGetter.getString(params, "VehicleID");
		
		int months[] = DateRegUtil.getMonthSql(begin, end);
		String toTimeDef = "";
		String workTimeDef = "";
		double toTimeDef1 = 0;
		double workTimeDef1 = 0;
		String TworkTimesql = "";// 发动机总运行时间参数sql
		for (int i = 0; i < months.length; i++) {
			TworkTimesql = TworkTimesql + "select MsgESS_Vcl_ID,"
					+ "MsgESS_MsgTime,"+ "MsgESS_State,"+ "MsgESS_Complete_1,"+ "MsgESS_Complete_2,"+ "vehiclerumkmid," + "MsgESS_iWork "
					+ "from "+GlobalDBVar.getDBFullName_Conn("Msg_EquipmentState_Statistics_"+months[i], "3", null)+""
					+ " inner join v_inteloil on vehicleid = msgess_vcl_id " +
					" union all ";
		}
		TworkTimesql = TworkTimesql.substring(0, TworkTimesql.length() - 10);
		String idleTworkTimeSql = "";// 总怠速运行时间参数sql
		for (int i = 0; i < months.length; i++) {
			idleTworkTimeSql = idleTworkTimeSql + "select MsgESS_Vcl_ID,"
					+ "MsgESS_MsgTime,"+ "MsgESS_State,"+ "MsgESS_Complete_1,"+ "MsgESS_Complete_2,"+ "vehiclerumkmid," + "MsgESS_iIdleWorkTime_UDS "
					+ "from "+GlobalDBVar.getDBFullName_Conn("Msg_EquipmentState_Statistics_"+months[i], "3", null)+""
					+ " inner join v_inteloil on vehicleid = msgess_vcl_id " +
					" union all ";
		}
		idleTworkTimeSql = idleTworkTimeSql.substring(0,
				idleTworkTimeSql.length() - 10);

		// 发动机运行时长每日和
		String TworkSql = "select SUM(MsgESS_iWork) AS SumTworkTime FROM ("
				+ TworkTimesql + ")ml" + " WHERE  ml.MsgESS_Vcl_ID = '" + vclid
				+ "' " + " and ml.MsgESS_MsgTime BETWEEN  '" + begin
				+ "' AND '" + end + "'";
				/*TworkSql=TworkSql+" and MsgESS_State = 0 ";
				TworkSql=TworkSql+" and( (MsgESS_Complete_1 = 11 and charindex(cast(vehiclerumkmid as varchar)+'1',cast(MsgESS_Complete_2 as varchar)) >0) ";
				TworkSql=TworkSql+"  or (MsgESS_Complete_1 = 00 and charindex(cast(vehiclerumkmid as varchar)+'0',cast(MsgESS_Complete_2 as varchar)) >0) ) ";*/
		Map<String, Object> mapTwork = baseDAO.execQueryOne(TworkSql,
				new Object[] {});
		if (null != mapTwork && !mapTwork.isEmpty()) {
			workTimeDef1 = MapGetter.getDouble(mapTwork, "SumTworkTime");
		}

		// 怠速每日和
		String TworkSql1 = "select SUM(MsgESS_iIdleWorkTime_UDS) AS SumTidleWorkTime FROM ("
				+ idleTworkTimeSql
				+ ")ml"
				+ " WHERE  ml.MsgESS_Vcl_ID = '"
				+ vclid
				+ "' "
				+ " and ml.MsgESS_MsgTime BETWEEN  '"
				+ begin
				+ "' AND '" + end + "'";
				/*TworkSql1=TworkSql1+" and MsgESS_State = 0 ";
				TworkSql1=TworkSql1+" and( (MsgESS_Complete_1 = 11 and charindex(cast(vehiclerumkmid as varchar)+'1',cast(MsgESS_Complete_2 as varchar)) >0) ";
				TworkSql1=TworkSql1+"  or (MsgESS_Complete_1 = 00 and charindex(cast(vehiclerumkmid as varchar)+'0',cast(MsgESS_Complete_2 as varchar)) >0) ) ";*/
		Map<String, Object> mapTwork1 = baseDAO.execQueryOne(
				TworkSql1, new Object[] {});
		if (null != mapTwork1 && !mapTwork1.isEmpty()) {
			toTimeDef1 = MapGetter.getDouble(mapTwork1, "SumTidleWorkTime");
		}

		toTimeDef = NumberFormat.doubleToString(toTimeDef1);// 怠速时长
		workTimeDef = NumberFormat.doubleToString(workTimeDef1);// 工作时长
		List<Map<String, Object>> reList = new ArrayList<Map<String, Object>>();
		Map<String, Object> ecmTimeMap = new HashMap<String, Object>();
		ecmTimeMap.put("ecmTotalTime", toTimeDef);
		reList.add(ecmTimeMap);
		Map<String, Object> ecmWorkTimeMap = new HashMap<String, Object>();
		ecmWorkTimeMap.put("ecmTotalWorkTime", workTimeDef);
		reList.add(ecmWorkTimeMap);
		return reList;
	}
	/**
	 * 档位及车速，查询延迟数据是否存在,若存在，求出此集合
	 * @param params
	 * @return
	 */
	public List<Map<String, Object>> getDelayDataOfGrASp() {
		//从标记表中获取上次执行的id
		String Recordsql="select fltmr_msgf_id as recordId from fltMsgRecord where fltmr_tableName='Msg_BufferRecord'";
		Map<String, Object> mapTwork = baseDAO.execQueryOne(Recordsql,new Object[] {});
		int recordId=StringUtil.isValid(MapGetter.getInt(mapTwork,"recordId"))?MapGetter.getInt(mapTwork,"recordId"):0;
		//获取大于标记表中，所有设备和统计时间
		String DelaySql="select MsgBF_ID Msgid,MsgBF_Vcl_ID Vclid,convert(varchar(10),MsgBF_Time,20) as MsgTime from "+GlobalDBVar.getDBFullName_Conn("Msg_BufferRecord", "3", null)+" where MsgBF_ID>"+recordId+" and MsgBF_MsgTp_ID=9  order by MsgBF_ID";
		List<Map<String,Object>>resultList=baseDAO.execQuery(DelaySql,new Object[] {});	
		return resultList;	
	}
	/**
	 * 将此次查询的缓存表中最大的id，更新至标记表
	 */
	public void updateMaxId(String maxId){
		MyConnectDSDAO ms=new MyConnectDSDAO();
		//将此Id更新至最大表中
		String updateSql=" update fltMsgRecord set fltmr_msgf_id="+maxId+",FLTMR_updateTime=getdate() where fltmr_tableName='Msg_BufferRecord'";
		ms.execUpdate(updateSql, new Object[] {});
		System.out.println("更新标记表成功，更新最大id为-------------"+maxId);
	}
	
	
	/**
	 * 查询设备DutyCycle有值的日期
	 * "累计结束点时" List.size() == 1
	 * 
	 **/
	public List<Map<String,Object>> getDateOfHaveDutyCycleData(String vehicleID,
			String begintime, String endtime,boolean finalPoint) {
		
		int months[] = DateRegUtil.getMonthSql(begintime, endtime);
		
		String tempsql="";
		for(int i=0;i<months.length;i++){
			if(i!=0){
				tempsql += " union all ";
			}
			tempsql += " select  CONVERT(VARCHAR(10),MsgESS_MsgDate,23) workStateTime, CONVERT(VARCHAR(10),MsgESS_MsgTime,23)  workTime "+
							"from "+GlobalDBVar.getDBFullName_Conn("Msg_EquipmentState_Statistics_"+months[i], "3", null)+" where MsgESS_Vcl_ID="+vehicleID+" and  MsgESS_MsgTime>='"+begintime+"' " +
									"and MsgESS_MsgTime<='"+endtime+"' and MsgESS_MsgDate is not null and MsgESS_MsgDate <>'' ";
		}
		StringBuffer sql = new StringBuffer();
		if(finalPoint){
			sql.append("select workStateTime,workTime from ("+tempsql+")temp ");
		}else{
			sql.append("select * from (select top 1 workStateTime,workTime  from ("+tempsql+") aa order by workTime asc " +
					"union all select top 1 workStateTime,workTime  from ("+tempsql+") bb order by workTime desc) temp order by workTime");
		}
		return this.getBaseDAO().execQuery(sql.toString(), new Object[]{});
	}
	
	
	/**
	 * 查询DutyCycle应答值
	 **/
	public String getDutyCycleReplyValue(String vehicleid,String flag,String tableDate,int sysu_id){
		String sql = "";
		if(159 == sysu_id){
			 sql = "SELECT top 1 MsgDCUC_Value FROM "+GlobalDBVar.getDBFullName_Conn("Msg_DutyCycle_UDS_Content_"+tableDate, "3", null)+" " 
					+"JOIN LINK_CTY_VCL.CTY_VCL.DBO.Sys_UDS ON MsgDCUC_UDSID = SysU_UDSID WHERE MsgDCUC_MsgDate=? and MsgDCUC_Vcl_ID=? and SysU_ID = ? ";
		} else {
			 sql = "SELECT MsgDCUC_Value FROM "+GlobalDBVar.getDBFullName_Conn("Msg_DutyCycle_UDS_Content_"+tableDate, "3", null)+" " 
					+"JOIN LINK_CTY_VCL.CTY_VCL.DBO.Sys_UDS ON MsgDCUC_UDSID = SysU_UDSID WHERE MsgDCUC_MsgDate=? and MsgDCUC_Vcl_ID=? and SysU_ID = ?   order by MsgDCUC_ValueIndex asc";
		}
		if("true".equals(flag)){
			sql+=" and MsgDCUC_MsgType=2 ";
		} else {
			sql+=" and (MsgDCUC_MsgType=2 or MsgDCUC_MsgType is null or MsgDCUC_MsgType='') ";
		}
		List<Map<String,Object>> list = this.getBaseDAO().execQuery(sql, new Object[]{tableDate,vehicleid,sysu_id});
		String result = "";
		for (Iterator<Map<String,Object>> iterator = list.iterator(); iterator.hasNext();) {
			result += MapGetter.getString(iterator.next(), "MsgDCUC_Value") + ",";
		}
		if(result.length() > 0){
			result = result.substring(0,result.length()-1);
		}
		return result;
	}
	
	/**
	 * 查询添加的手动统计任务
	 * @return
	 */
	public List<Map<String, Object>> getHandVclInfo(){
		MyBaseDAO mba = new MyBaseDAO();
		StringBuffer sbSQl = new StringBuffer();
		sbSQl.append("select DB2TI_ID,DB2TI_VclID,DB2TI_AccountID,DB2TI_State,convert(varchar(19),DB2TI_InsertTime,120) as DB2TI_InsertTime," +
				" convert(varchar(19),DB2TI_UpdateTime,120) as DB2TI_UpdateTime,convert(varchar(10),DB2TI_BeTime,120) as DB2TI_BeTime," +
				" convert(varchar(10),DB2TI_EndTime,120) as DB2TI_EndTime from DB2_TaskInfo ");
		sbSQl.append(" where DB2TI_State = 0 or DB2TI_State = 1 ");
		List<Map<String,Object>> list = mba.getBaseDAO().execQuery(sbSQl.toString(), new Object[]{});
		return list;
	}
	
	/**
	 * 根据设备号获取设备ID
	 * @param vehicleNumber
	 * @return
	 */
	public Map<String, Object> getVclInfoByNum(String vehicleNumber){
		MyBaseDAO mba = new MyBaseDAO();
		StringBuffer sbSQl = new StringBuffer();
		sbSQl.append(" select vehicleID from v_vehicleInfo where VehicleNumber = '"+vehicleNumber+"' ");
		Map<String, Object> mapData = new HashMap<String,Object>();
		mapData = mba.getBaseDAO().execQueryOne(sbSQl.toString(), new Object[]{});
		return mapData;
		
	}
	/**
	 * 更新任务状态
	 * @return
	 */
	public boolean updateTaskInfo(String id,int state){
		MyBaseDAO mba = new MyBaseDAO();
		StringBuffer sbSQl = new StringBuffer();
		sbSQl.append("update DB2_TaskInfo set DB2TI_State = "+state+" ");
		sbSQl.append(" where DB2TI_ID = "+id+" ");
		int i  = mba.getBaseDAO().execUpdate(sbSQl.toString(), new Object[]{});
		if(i>0){
			return true;
		} else {
			return false;
		}
	}
	
	public void initparamDao(){
		baseDAO = new BaseDAO();
		tranDAO = new TransactionDAO();
		ProxoolDataSource connGetter = new ProxoolDataSource();
		connGetter.setProxoolname(proxool);
		((BaseDAO) baseDAO).setConnGetter(connGetter);
		((TransactionDAO) tranDAO).setConnGetter(connGetter);
	}

}
